import pandas as pd
import matplotlib.pyplot as plt
import json
import os
from pathlib import Path

def process_file(file_name,output_dir):
    # 读取Excel文件
    df = pd.read_excel(file_name, sheet_name='sheet1')

    # 处理表头，保留中文部分
    df.columns = [col.split('/')[0] for col in df.columns]

    # 保存处理后的Excel文件
    serial_number = file_name.split('.')[0]
    output_folder = os.path.join(output_dir, serial_number)
    os.makedirs(output_folder, exist_ok=True)
    print(output_folder)

    # 处理表头，保留中文部分
    df.columns = [col.split('/')[0] for col in df.columns]

    print(df.head())

    # 1. 电池序列号
    battery_serial_number = file_name.split('.')[0]

    # 2. 获取电池数据时间范围
    data_time_range = df['系统时间'].min(), df['系统时间'].max()

    # 3. 绘制双坐标散点图
    # 3. 绘制系统时间对SOC和总电压的双坐标散点图
    fig1, ax1 = plt.subplots()

    color = 'tab:red'
    ax1.set_xlabel('System Time')
    ax1.set_ylabel('SOC', color=color)
    #ax1.plot(df['系统时间'], df['SOC'], color=color)
    ax1.tick_params(axis='y', labelcolor=color)

    ax2 = ax1.twinx()  # 实例化一个第二个坐标轴，与ax1共享同一个X轴
    color = 'tab:blue'
    ax2.set_ylabel('Total Voltage', color=color)
    #ax2.plot(df['系统时间'], df['总电压'], color='tab:blue', linestyle='--')
    ax2.tick_params(axis='y', labelcolor=color)

    fig1.tight_layout()  # 调整布局
    plt.title('Battery SOC and Voltage over Time')
    plt.savefig(os.path.join(output_folder, f'{serial_number}_voltage.png'))  # 保存图片
    plt.close(fig1)

    # 4. 绘制系统时间对SOC和总电流的双坐标散点图
    fig2, ax3 = plt.subplots()

    color = 'tab:red'
    ax3.set_xlabel('System Time')
    ax3.set_ylabel('SOC', color=color)
    #ax3.plot(df['系统时间'], df['SOC'], color=color)
    ax3.tick_params(axis='y', labelcolor=color)

    ax4 = ax3.twinx()  # 实例化一个第二个坐标轴，与ax3共享同一个X轴
    color = 'tab:blue'
    ax4.set_ylabel('Total Current', color=color)
    #ax4.plot(df['系统时间'], df['总电流'], color='tab:blue', linestyle='-.')
    ax4.tick_params(axis='y', labelcolor=color)

    fig2.tight_layout()  # 调整布局
    plt.title('Battery SOC and Current over Time')
    plt.savefig(os.path.join(output_folder, f'{serial_number}_current.png'))  # 保存图片
    plt.close(fig2)

    # 4. 获取最高单体电压值及对应的电池单体代号
    max_voltage_cell = df[['电池单体电压最高值', '最高电压电池单体代号']].max().tolist()

    # 5. 获取最低单体电压值及对应的电池单体代号
    min_voltage_cell = df[['电池单体电压最低值', '最低电压电池单体代号']].min().tolist()

    # 6. 获取电池压差最大值的行数据
    max_voltage_differential_row = df.loc[df['电池压差'].idxmax()]

    # 7. 获取最高温度值
    max_temperature = df['最高温度值'].max()

    with open(os.path.join(output_folder, f'{serial_number}.txt'), 'w', encoding='utf-8') as f:
        f.write(f"电池序列号: {battery_serial_number}\n")
        f.write(f"数据时间范围: {data_time_range[0]} 到 {data_time_range[1]}\n")
        f.write(f"最高单体电压值: {max_voltage_cell[0]}, 电池单体代号: {max_voltage_cell[1]}\n")
        f.write(f"最低单体电压值: {min_voltage_cell[0]}, 电池单体代号: {min_voltage_cell[1]}\n")
        f.write(f"电池压差最大值: {max_voltage_differential_row['电池压差']}\n")
        f.write(f"相关数据: {max_voltage_differential_row.to_dict()}\n")
        f.write(f"最高温度值: {max_temperature}\n")

    print(f'分析完成，结果已保存到 {battery_serial_number}_voltage.png, {battery_serial_number}_current.png 和 {battery_serial_number}.txt')
def find_excel_files_with_keyword(root_dir, keyword):
    excel_files = []
    for dirpath, dirnames, filenames in os.walk(root_dir):
        for filename in filenames:
            if keyword in filename and filename.endswith(('.xls', '.xlsx')):
                excel_files.append(os.path.join(dirpath, filename))
    return excel_files

root_dir = r"C:\Users\A0080437\Downloads\8、第八部分 43台 五得利25日收到数据（有照片）"
keyword = '数据'
excel_files = find_excel_files_with_keyword(root_dir, keyword)

#excel_files = [f for f in os.listdir(input_dir) if f.endswith('.xlsx')]

for file in excel_files:
    process_file(os.path.join(root_dir, file), root_dir)
